﻿Imports DTO
Imports System.Data.OleDb

Public Class NhanVienDAO
    Public Function LayThongTinNhanVien(ByVal idNhanVien As String, ByVal matkhau As String) As DataTable
        Dim nhvienDTo As New DataTable
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.ConnectDB()
        Dim strSQL = "SELECT nv.ID,nv.IDNhanVien,nv.MatKhau,nv.TenNhanVien,nv.DiaChi,nv.DienThoai,nv.NgaySinh,nv.MaChucVu,cv.TenChucVu"
        strSQL += " FROM NhanVien nv,ChucVu cv"
        strSQL += " WHERE nv.MaChucVu = cv.MaChucVu And nv.IDNhanVien = '" + idNhanVien.ToString + "' And nv.MatKhau = '" + matkhau + "'"
        Dim Comand As New OleDbCommand(strSQL, cnn)
        Dim adapter As New OleDbDataAdapter(Comand)
        adapter.Fill(nhvienDTo)
        cnn.Close()
        Return nhvienDTo
    End Function

    Public Function ThemNhanVien(ByVal nvDTo As NhanVienDTO) As Integer
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.ConnectDB()
        Dim comand As New OleDbCommand()
        Dim strSQL = "INSERT INTO NHANVIEN(IDNhanVien,MatKhau,TenNhanVien,MaChucVu,DiaChi,NgaySinh,DienThoai)VALUES(?,?,?,?,?,?,?)"
        comand.Parameters.Add("@IDNhanVien", OleDbType.WChar)
        comand.Parameters.Add("@MatKhau", OleDbType.WChar)
        comand.Parameters.Add("@TenNhanVien", OleDbType.WChar)
        comand.Parameters.Add("@MaChucVu", OleDbType.Integer)
        comand.Parameters.Add("@DiaChi", OleDbType.WChar)
        comand.Parameters.Add("@NgaySinh", OleDbType.Date)
        comand.Parameters.Add("@DienThoai", OleDbType.WChar)

        comand.Parameters("@IDNhanVien").Value = nvDTo.IDNhanVien
        comand.Parameters("@MatKhau").Value = nvDTo.MatKhau
        comand.Parameters("@TenNhanVien").Value = nvDTo.TenNhanVien
        comand.Parameters("@MaChucVu").Value = nvDTo.MaChucVu
        comand.Parameters("@DiaChi").Value = nvDTo.DiaChi
        comand.Parameters("@NgaySinh").Value = nvDTo.NgaySinh.Date()
        comand.Parameters("@DienThoai").Value = nvDTo.DienThoai
        comand.CommandText = strSQL
        comand.Connection = cnn
        Dim kq As Integer = comand.ExecuteNonQuery()
        cnn.Close()
        Return kq
    End Function

    Public Function LayDanhSachNhanVien() As DataTable
        Dim nhvienDTo As New DataTable
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.ConnectDB()
        Dim strSQL = "SELECT nv.ID,nv.IDNhanVien,nv.MatKhau,nv.TenNhanVien,nv.DiaChi,nv.DienThoai,nv.NgaySinh,nv.MaChucVu,cv.TenChucVu"
        strSQL += " FROM NhanVien nv,ChucVu cv"
        strSQL += " WHERE nv.MaChucVu = cv.MaChucVu AND nv.IsDelete = 1"
        strSQL += " Group By nv.ID,nv.IDNhanVien,nv.MatKhau,nv.TenNhanVien,nv.DiaChi,nv.DienThoai,nv.NgaySinh,nv.MaChucVu,cv.TenChucVu"
        Dim Comand As New OleDbCommand(strSQL, cnn)
        Dim adapter As New OleDbDataAdapter(Comand)
        adapter.Fill(nhvienDTo)
        cnn.Close()
        Return nhvienDTo
    End Function

    Public Function Xoa(ByVal ID As Integer) As Integer
        Dim kq As Integer
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.ConnectDB()
        Dim strSQL = "UPDATE NHANVIEN SET IsDELETE = 2 WHERE ID = " + ID.ToString()

        Dim Comand As New OleDbCommand(strSQL, cnn)
        kq = Comand.ExecuteNonQuery()
        cnn.Close()
        Return kq
    End Function

    Public Function KiemTraTonTaiNhanVien(ByVal nv As String) As DataTable
        Dim cnn As New OleDbConnection()
        cnn = DataProvider.ConnectDB()
        Dim strSQL = "SELECT ID FROM NhanVien WHERE IDNhanVien = '" + nv.ToString() + "'"
        Dim dt As New DataTable()
        Dim comand As New OleDbCommand()
        comand.CommandText = strSQL
        comand.Connection = cnn
        Dim adapter As New OleDbDataAdapter(comand)
        adapter.Fill(dt)
        Return dt
    End Function


End Class
